/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package SalaryComponent;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Vector;

/**
 *
 * @author Mukesh
 */
public class Query {

    static Connection con = null;

    public static int add(SalaryComponentBean scb) throws SQLException {
        int i = 0, k = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into salary_component values(SALARYCOMPONENTID.nextval,?,?,?,sysdate)");
            pre.setString(1, scb.getSalaryComponent());
            pre.setInt(2, scb.getSalaryType());
            pre.setInt(3, scb.getValueType());
            i = pre.executeUpdate();
            if (i != 0) {
                k = getMaxId();
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return k;
    }

    public static int getMaxId() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select max(SALARY_COMPONENT_ID) from salary_component");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                i = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return i;
    }

    public static int getMaxEmpSalId() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select max(EMP_SAL_ID) from EMPLOYEE_SALARY");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                i = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return i;
    }
    /*public static SalaryComponentBean viewSpecific(String cname, String val) throws SQLException {
     ProgramSeatBean pb = new ProgramSeatBean();
     CourseBean cb = new CourseBean();
     try {
     con = DBConnection.Connection.getConnection();
     PreparedStatement pre = con.prepareStatement("select * from program_seat left join course on program_seat.course_id=course.course_id where " + cname + "=?");
     ResultSet rs = pre.executeQuery();
     while (rs.next()) {
     pb.setProgramSeatId(rs.getInt(1));
     pb.setCourseId(rs.getInt(2));
     pb.setStream(rs.getString(3));
     pb.setTotalSeat(rs.getInt(4));
     pb.setProgramSeatUnique(rs.getString(5));
     pb.setEntryDate(rs.getDate(6));
     cb.setCourseId(rs.getInt(7));
     cb.setCourseName(rs.getString(8));
     cb.setCourseType(rs.getInt(9));
     cb.setCoursePeriod(rs.getInt(10));
     cb.setStream(rs.getString(11));
     cb.setEntryDate(rs.getDate(12));
     pb.setCourseBean(cb);

     }
     } catch (Exception e) {
     System.out.println(e);
     } finally {
     con.close();
     }
     return pb;
     }*/

    public static Vector viewAll() throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {

            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from salary_component order by salary_component_id");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                SalaryComponentBean scb = new SalaryComponentBean();
                scb.setSalaryComponentId(rs.getInt(1));
                scb.setSalaryComponent(rs.getString(2));
                scb.setSalaryType(rs.getInt(3));
                scb.setValueType(rs.getInt(4));
                list.add(scb);

            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static int edit(SalaryComponentBean scb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update salary_component set component_name=?, salary_type=?, value_type=?, entrydate=sysdate where salary_component_id=?");
            pre.setString(1, scb.getSalaryComponent());
            pre.setInt(2, scb.getSalaryType());
            pre.setInt(3, scb.getValueType());
            pre.setInt(4, scb.getSalaryComponentId());
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int delete(int id) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("delete from SALARY_COMPONENT where SALARY_COMPONENT_ID=?");
            pre.setInt(1, id);
            i = pre.executeUpdate();
        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }

    public static int addEmpSal(EmpSalaryBean empSalB) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into EMPLOYEE_SALARY values(EMPSALID.nextval,?,?,?,?,sysdate)");
            pre.setInt(1, empSalB.getUserId());
            pre.setFloat(2, empSalB.getTotalEarning());
            pre.setFloat(3, empSalB.getTotalDeduction());
            pre.setFloat(4, empSalB.getTotalSal());
            i = pre.executeUpdate();
            if (i != 0) {
                i = getMaxEmpSalId();
            }
        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }

    public static int editEmpSal(EmpSalaryBean empSalB) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update EMPLOYEE_SALARY set USER_ID=?,TOTAL_EARNING=?,TOTAL_DEDUCTION=?,TOTAL_SALARY=?,ENTRY_DATE=sysdate where EMP_SAL_ID=?");
            pre.setInt(1, empSalB.getUserId());
            pre.setFloat(2, empSalB.getTotalEarning());
            pre.setFloat(3, empSalB.getTotalDeduction());
            pre.setFloat(4, empSalB.getTotalSal());
            pre.setFloat(5, empSalB.getEmpSalId());
            i = pre.executeUpdate();
            if (i != 0) {
                i = empSalB.getEmpSalId();
            }
        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }

    public static int chkEmpSal(int empsalid) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select emp_sal_id from EMPLOYEE_SALARY where USER_ID=?");
            pre.setInt(1, empsalid);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                i = rs.getInt(1);
            }

        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }

    public static int deleteEmpSal(int empsalid) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("delete from EMP_SAL_DETAIL where EMP_SAL_ID=?");
            pre.setInt(1, empsalid);
            i = pre.executeUpdate();

        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }

    public static int addEmpSalDetails(Vector list) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into EMP_SAL_DETAIL values(EMPSALDETAIL.nextval,?,?,?,sysdate)");
            Iterator it = list.iterator();
            while (it.hasNext()) {
                EmpSalDetailsBean esdb = (EmpSalDetailsBean) it.next();
                pre.setInt(1, esdb.getEmpSalId());
                pre.setFloat(2, esdb.getSalCompoId());
                pre.setFloat(3, esdb.getAmount());
                pre.addBatch();
            }
            int[] k = pre.executeBatch();
            if (k.length != 0) {
                i = 1;
            }
        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }

    public static EmpSalaryBean viewAllEmpSal(int empid) throws SQLException {
        EmpSalaryBean esb = new EmpSalaryBean();
        try {

            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from employee_salary where user_id=?");
            pre.setInt(1, empid);
            ResultSet rs = pre.executeQuery();

            while (rs.next()) {

                esb.setEmpSalId(rs.getInt(1));
                esb.setUserId(rs.getInt(2));
                esb.setTotalEarning(rs.getFloat(3));
                esb.setTotalDeduction(rs.getFloat(4));
                esb.setTotalSal(rs.getFloat(5));
                esb.setEntryDate(rs.getDate(6));
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return esb;
    }
    public static Vector viewAllEmpSalDetail(int empsalid) throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from emp_sal_detail where emp_sal_id=?");
            pre.setInt(1, empsalid);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                EmpSalDetailsBean esd = new EmpSalDetailsBean();
                esd.setEmpSalDetId(rs.getInt(1));
                esd.setEmpSalId(rs.getInt(2));
                esd.setSalCompoId(rs.getInt(3));
                esd.setAmount(rs.getFloat(4));
                esd.setEntryDate(rs.getDate(5));
                list.add(esd);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
}
